package org.gulugulubiao;

import org.gulugulubiao.entity.JudgeResult;
import org.gulugulubiao.service.Impl.ProblemServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// import org.gulugulubiao.mapper.DishMapper;

@SpringBootTest
public class testUpdateProblem {
    private ProblemServiceImpl problemService;
    // @Autowired
    // private DishMapper dishMapper;
//    private String problem = "insert into user (id,openid,name,phone,sex,id_number,avatar,create_time) values (1,1,1,1,1,1,1,'2024-11-21 09:34:02')";
//    private String answer = "insert into user (id,openid,name,phone,sex,id_number,avatar,create_time) values (1,1,1,1,1,1,1,'2024-11-22 09:34:02')";
//    private String sql = "select * from user";
    private String problem = "INSERT INTO students (student_id,name, gender, birth_date, email)\n" +
            "VALUES (16,'Jessica Adams', 'Female', '2000-02-20', 'jessica.adams@exsssssample.com');\n";
    private String answer = "INSERT INTO students (student_id,name, gender, birth_date, email)\n" +
            "VALUES (17,'Jessica Adams', 'Female', '2000-02-10', 'jessica.adams@11exaaaample.com');\n";
    private String sql = "select * from students";

    @Test
    public void test(){
        Connection conn = null;
        Statement statement =null;

        try {
            // 获取数据库连接
//            String url = "jdbc:mysql://localhost:3306/test";
//            String username = "root";
//            String password = "123456";
            String url = "jdbc:mysql://120.79.72.63:3307/sqlOnline";
            String username = "root";
            String password = "sqlOJ2025$$02qweokn!@#&1das";
            conn = DriverManager.getConnection(url, username, password);

            // 关闭事务自动提交，改为手动提交
            conn.setAutoCommit(false);


            // 获取预编译SQL语句对象
            statement = conn.createStatement();

            // 执行SQL操作
            statement.execute(problem);
            //获取整张表
            ResultSet problemlist = statement.executeQuery(sql);
            // 获取元数据
            ResultSetMetaData metaData = problemlist.getMetaData();
            int columnCount = metaData.getColumnCount(); // 获取列数
            List<Map<String, Object>> inputResultList = new ArrayList<>();

            // 动态处理结果
            while (problemlist.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i); // 获取列名
                    Object value = problemlist.getObject(i); // 获取列值
                    rowMap.put(columnName, value); // 存储列名和值
                }
                inputResultList.add(rowMap); // 将当前行数据添加到结果列表
            }

            //conn.commit();
            // 回滚事务
            conn.rollback();

            // 执行SQL操作
            statement.execute(answer);
            System.out.println(answer);

            //获取整张表
            ResultSet answerlist = statement.executeQuery(sql);
            // 获取元数据
            metaData = answerlist.getMetaData();
            // 获取列数
            columnCount = metaData.getColumnCount();
            List<Map<String, Object>> rightResultList = new ArrayList<>();
            // 动态处理结果
            while (answerlist.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i); // 获取列名
                    Object value = answerlist.getObject(i); // 获取列值
                    rowMap.put(columnName, value); // 存储列名和值
                }
                rightResultList.add(rowMap); // 将当前行数据添加到结果列表
            }

            JudgeResult judgeResult = new JudgeResult();
            judgeResult.setAnswer(answer);
            if (compareResultLists(inputResultList, rightResultList)) {
                judgeResult.setFlag(true);
            } else {
                judgeResult.setFlag(false);
            }
            System.out.println(judgeResult);


            // 回滚事务
            conn.rollback();

        }catch (Exception e){
            e.printStackTrace();
        }

    }

    // 比较两个List<Map<String, Object>>是否相等
    private boolean compareResultLists(List<Map<String, Object>> list1, List<Map<String, Object>> list2) {
        if (list1.size() != list2.size()) {
            return false; // 行数不同
        }

        // 逐行比较，假设行的顺序可以不同
        List<Map<String, Object>> copyOfList2 = new ArrayList<>(list2);

        for (Map<String, Object> row1 : list1) {
            boolean matchFound = false;
            for (Map<String, Object> row2 : copyOfList2) {
                if (row1.equals(row2)) {
                    copyOfList2.remove(row2);
                    matchFound = true;
                    break;
                }
            }
            if (!matchFound) {
                return false; // 没找到匹配的行
            }
        }
        return true; // 所有行均匹配
    }
}
